const express = require('express');
const router = express.Router();
const con = require('../db/mysql');

router.get('/', async (req, res) => {
    const { page, limit, industry, state, company } = req.query;
    try {
        let where = "";
        if (industry) {
            where += ` AND customer.industry = ${industry}`
        }
        if (state) {
            where += ` AND customer.state = ${state}`
        }
        if (company) {
            where += ` AND customer.company LIKE '%${company}%' `
        }
        const result = await con.query(`SELECT customer.id,customer.company,customer.industry,customer.province,customer.name,customer.phone,customer.title,customer.needs,customer.state,manager.username FROM  customer,manager WHERE customer.mid=manager.id ${where} ORDER BY customer.id DESC LIMIT ${limit} OFFSET ${page * limit - limit};SELECT COUNT(*) AS total FROM customer WHERE 1 ${where}`);
        if (result.length) {
            res.json({
                msg: "查询成功",
                code: 200,
                data: result[0],
                total: result[1][0].total
            });
        }
    } catch (error) {
        throw error;
    }
})
router.post('/', async (req, res) => {
    const { company, industry, province, name, phone, title, state, mid, needs } = req.body;
    try {
        const result = await con.query(`INSERT INTO customer (company,industry,province,name,phone,title,state,mid,needs) VALUES (?,?,?,?,?,?,?,?,?)`, [company, industry, province, name, phone, title, state, mid, needs]);
        if (result.affectedRows === 1) {
            res.json({
                msg: "新增成功",
                code: 200,
            })
        } else {
            res.json({
                msg: "新增失败",
                code: 500,
            })
        }
    } catch (error) {
        throw error;
    }
})
router.delete('/:id', async (req, res) => {
    const { id } = req.params;
    try {
        const result = await con.query(`DELETE FROM customer WHERE id=?`, id);
        if (result.affectedRows === 1) {
            res.json({
                msg: "删除成功",
                code: 200,
            })
        } else {
            res.json({
                msg: "删除失败",
                code: 500,
            })
        }
    } catch (error) {
        throw error;
    }
})
router.get('/:id', async (req, res) => {
    const { id } = req.params;
    try {
        const result = await con.query(`SELECT * FROM customer WHERE id=?`, id);
        if (result.length) {
            res.json({
                msg: "查询成功",
                code: 200,
                data: result[0]
            })
        } else {
            res.json({
                msg: "查询失败",
                code: 500,
            })
        }
    } catch (e) {
        throw e;
    }
})
router.put('/:id', async (req, res) => {
    const { id } = req.params;
    const { company, industry, province, name, phone, title, state, mid, needs } = req.body;
    try {
        const result = await con.query(`UPDATE customer SET company=?, industry=?, province=?, name=?, phone=?, title=?, state=?, mid=?, needs=? WHERE id=?`, [company, industry, province, name, phone, title, state, mid, needs, id]);
        if (result.changedRows === 1) {
            res.json({
                msg: "修改成功",
                code: 200,
            })
        } else {
            res.json({
                msg: "未修改任何数据或修改失败",
                code: 500,
            })
        }

    } catch (error) {
        throw error;
    }
})
module.exports = router;